package com.winton.dao.impl;

import java.util.List;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.winton.Z.Page;
import com.winton.dao.ItemDao;
import com.winton.model.S_ITEM;

@Repository
public class ItemDaoImpl extends DaoBase implements ItemDao{
	
	@Override
	public String addItem(S_ITEM item) {
		
		StringBuilder sBuilder=new StringBuilder();
		sBuilder.append("insert into S_ITEM(id,name,pid,showorder,style,systime,userid,url)");
		sBuilder.append(" values (seq_itemid.nextval,?,?,?,?,sysdate,?,?)");
		
		Object[] objArr = item.getInsertFields();
		int count = jdbcTemplate.update(sBuilder.toString(), objArr);
		
		return (count > 0)+"";
	}

	@Override
	public List<S_ITEM> getItemListByUserid(int userid) {
		
		RowMapper<S_ITEM> row = new BeanPropertyRowMapper<>(S_ITEM.class);
		StringBuilder sBuilder=new StringBuilder();
		sBuilder.append(" select * from S_ITEM where id in( ");
		sBuilder.append(" select itemid from S_USERITEM where userid=? ");
		sBuilder.append(" ) order by showorder ");
		
		List<S_ITEM> list = jdbcTemplate.query(sBuilder.toString(), row, new Object[]{userid});
		
		return list;
	}

	@Override
	public int selectItemListCount(String strWhere, Object[] objArr) {
		StringBuilder sBuilder=new StringBuilder();
		sBuilder.append("select count(1) from S_ITEM");
		sBuilder.append(strWhere.length() > 0 ? " where " + strWhere : "");
		Integer count = jdbcTemplate.queryForObject(sBuilder.toString(), Integer.class, objArr);
		return count;
	}

	@Override
	public List<S_ITEM> queryItemList(Page page, String strWhere, Object[] objArr) {
		RowMapper<S_ITEM> row = new BeanPropertyRowMapper<>(S_ITEM.class);
		StringBuilder sBuilder=new StringBuilder();
		sBuilder.append("select t1.* from (");
		sBuilder.append(" select ROWNUM as r,t.* from (");
		sBuilder.append("  select * from S_ITEM");
		sBuilder.append(strWhere.length() > 0 ? " where " + strWhere : "");
		sBuilder.append("  order by pid , showorder ");
		sBuilder.append(" ) t");
		sBuilder.append(" where ROWNUM<=" + (page.getStart() + page.getRow()));
		sBuilder.append(") t1");
		sBuilder.append(" where t1.r>" + page.getStart());
		List<S_ITEM> list = jdbcTemplate.query(sBuilder.toString(), row, objArr);
		return list;
	}

	@Override
	public List<S_ITEM> queryLevelItem() {		
		RowMapper<S_ITEM> row = new BeanPropertyRowMapper<>(S_ITEM.class);	
		
		String sql = "select * from S_ITEM where pid=0 ";
		
		List<S_ITEM> list = jdbcTemplate.query(sql, row);
		
		return list;
	}

	@Override
	public int deleteItemById(int id) {
		
		StringBuilder sBuilder=new StringBuilder();
		sBuilder.append("delete from S_ITEM  where id=? ");
		
		Object[] objArr = new Object[]{id};
		int count = jdbcTemplate.update(sBuilder.toString(), objArr);
		
		return count ;
	}

	@Override
	public String updateItem(S_ITEM item) {
		StringBuilder sBuilder=new StringBuilder();
		sBuilder.append("update S_ITEM set name=?,pid=?,showorder=?,style=?,url=? where id= ? ");
				
		Object[] objArr = item.getUpdateFields();
		int count = jdbcTemplate.update(sBuilder.toString(), objArr);
		
		return (count > 0)+"";
	}

	@Override
	public List<S_ITEM> queryItemList() {
		RowMapper<S_ITEM> row = new BeanPropertyRowMapper<>(S_ITEM.class);	
		
		String sql = "select * from S_ITEM ";
		
		List<S_ITEM> list = jdbcTemplate.query(sql, row);
		
		return list;
	}

	@Override
	public S_ITEM findItemById(int id) {
		RowMapper<S_ITEM> row = new BeanPropertyRowMapper<>(S_ITEM.class);			
		String sql = "select * from S_ITEM where id=? ";		
		S_ITEM item = jdbcTemplate.queryForObject(sql,row,new Object[]{id});		
		return item;
	}

	@Override
	public List<S_ITEM> findItemByPid(int id) {
		RowMapper<S_ITEM> row = new BeanPropertyRowMapper<>(S_ITEM.class);
		String sql = " select * from S_ITEM where pid=? ";
		
		List<S_ITEM> list = jdbcTemplate.query(sql, row, new Object[]{id});
		
		return list;
	}

	

}
